SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION dbo.instrumentRollup
 (
      @NAVDate SMALLDATETIME
 )
 RETURNS @rollup TABLE
      (
			FS_PricingId VARCHAR(20) 
			
           ,instrumentName VARCHAR(100)
         , Portfolio      VARCHAR(100)
         , par            DECIMAL(38, 5)
         , marketValue    DECIMAL(38, 5)
         , amortizedCost  DECIMAL(38, 5)
         , UnrealizedApp  DECIMAL(38, 5)
         , UnrealizedDep  DECIMAL(38, 5)
		 
      )
      AS BEGIN
 INSERT INTO @rollup
 SELECT 
		i.ExtId
		,max(i.InstrName)
    , a.PortfolioId
    , SUM(a.EndAdjAmt)
    , SUM(a.MktVal)
    , SUM(a.AmortCost)
    , SUM(a.UnrealApprec)
    , SUM(a.UnrealDeprec)
 FROM FSFundModel.Reporting.Instrument i
      INNER JOIN FSFundModel.Reporting.Asset a
      ON   a.ExportId     = i.ExportId
      AND  a.InstrumentId = i.InstrumentId
      INNER JOIN FSFundModel.Reporting.Portfolio p
      ON   a.ExportId    = p.ExportId
      AND  a.PortfolioId = p.PortfolioId
 WHERE i.ExportId        =
      (SELECT MAX(e.ExportId)
      FROM FSFundModel.Reporting.Export e
      WHERE e.FundId = 'FSIC'
	  AND e.NAVDate = @NAVDate)
 AND  a.IsActiveEndOfDay = 1
 GROUP BY i.ExtId
    , a.PortfolioId
 RETURN;
 
 END;

GO
